﻿using jinmaMesProxy.Model;
using Microsoft.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace jinmaMesProxy.DataAccess
{
    public class ErpVouchQuery<TMain, TDetail>
        where TMain : MainDetailModel<TDetail>,new()
        where TDetail : DetailModel,new()
    {
        private string SelectSql<T>()
        {
            var table = typeof(T).GetCustomAttribute<MyTableNameAttribute>().TableName;
            StringBuilder sb = new StringBuilder();
            var ps = typeof(T).GetProperties();
            foreach (var p in ps)
            {
                var att = p.GetCustomAttribute<MyColumnNameAttribute>();
                if (att == null) continue;
                sb.Append(att.ColumnName + ",");
            }
            sb.Remove(sb.Length - 1, 1);
            return $"SELECT {sb.ToString()} FROM {table} AS T WHERE 1=1 " ;
        }

        public List<TMain> Query(string where, params KeyValuePair<string, object>[] p)
        {
            ErpDbInstance erpDbInstance = ServiceLoader.GetService<ErpDbInstance>();
            List<TMain> result = new List<TMain>();
            var baseSql = SelectSql<TMain>() + where;
            var ps = typeof(TMain).GetProperties();
            using (var cmd = erpDbInstance.MakeQueryCommand(baseSql,p))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        TMain r = new TMain();
                        foreach (var pi in ps)
                        {
                            Utils.SetVouchFromReader(result, pi, reader);
                        }
                        result.Add(r);
                    }
                }
            }
            return result;
        }

        public bool Exists(string billCode)
        {
            ErpDbInstance erpDbInstance = ServiceLoader.GetService<ErpDbInstance>();
            var table = typeof(TMain).GetCustomAttribute<MyTableNameAttribute>()!.TableName;
            var bill = typeof(TMain).GetProperty(nameof(MainDetailModel<TDetail>.BillCode))!
                .GetCustomAttribute<MyColumnNameAttribute>()!.ColumnName;
            var baseSql = $"SELECT COUNT(1) FROM {table} WHERE {bill} = @billCode";
            using (var cmd = erpDbInstance.MakeQueryCommand(baseSql,new KeyValuePair<string, object?>("@billCode",billCode)))
            {
                return ((int)cmd.ExecuteScalar() > 0);
            }
        }
        public TMain? GetByCode(string billCode)
        {
            ErpDbInstance erpDbInstance = ServiceLoader.GetService<ErpDbInstance>();
            TMain result = null;
            var baseSql = SelectSql<TMain>() 
                + String.Format(" AND {0} = @billCode", 
                    typeof(TMain).GetProperty(nameof(MainDetailModel<TDetail>.BillCode))!
                    .GetCustomAttribute<MyColumnNameAttribute>()!.ColumnName);
            var detailSql = SelectSql<TDetail>()
                + String.Format(" AND {0} = @Id",
                    typeof(TMain).GetProperty(nameof(MainDetailModel<TDetail>.Id))!
                    .GetCustomAttribute<MyColumnNameAttribute>()!.ColumnName);
            var ps = typeof(TMain).GetProperties();
            var dps = typeof(TDetail).GetProperties();

            using (var cmd = erpDbInstance.MakeQueryCommand(baseSql,new KeyValuePair<string, object?>("@billCode",billCode)))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        result = new TMain();
                        foreach (var p in ps)
                        {
                            Utils.SetVouchFromReader(result, p, reader);
                        }
                    }
                    else
                        return null;
                }
            }
            

            using (var cmd = erpDbInstance.MakeQueryCommand(detailSql, new KeyValuePair<string, object?>("@ID", result.Id)))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    result.Details = new List<TDetail>();
                    while (reader.Read())
                    {
                        TDetail detail = new TDetail();
                        foreach (var p in dps)
                        {
                           Utils.SetVouchFromReader(detail, p, reader);
                        }
                        result.Details.Add(detail);
                    }
                }
            }
            return result;
        }
        
    }
}
